package com.lz.testcase.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * @description
 *
 * @date 2021-08-12 17:19
 *
 * @author Lizhong
 */
public class NomalDemo {
    // static String url = "jdbc:mysql://10.10.11.107:13306/scm_inventory?Unicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai";
    static String url = "jdbc:mysql://47.104.237.35/test?Unicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai&useCursorFetch=true";
    static String user = "root";
    static String pwd = "";
    private static String sql= "select * from test_insert  ";


    public static void selectCurers() throws SQLException, ClassNotFoundException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection connection = DriverManager.getConnection(url, user, pwd);
        PreparedStatement statement = connection.prepareStatement("select * from test_insert ",
                ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
        statement.setFetchSize(1);
        long begin = System.currentTimeMillis();
        ResultSet resultSet = statement.executeQuery();

        while (resultSet.next()) {
            System.out.println(resultSet.getString(1));
        }
        long end = System.currentTimeMillis();
        System.out.println("selectStream span time="+(end-begin) + "ms");
        resultSet.close();
        statement.close();
        connection.close();
    }
    public static void selectNormal() throws SQLException, ClassNotFoundException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection connection = DriverManager.getConnection(url, user, pwd);
        PreparedStatement statement = connection.prepareStatement("select * from test_insert  limit 5000",
                ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
        long begin = System.currentTimeMillis();
        ResultSet resultSet = statement.executeQuery();
        /*
         *"main@1" prio=5 tid=0x1 nid=NA runnable
  java.lang.Thread.State: RUNNABLE
	  at com.mysql.cj.protocol.a.NativeProtocol.read(NativeProtocol.java:1588)
	  at com.mysql.cj.protocol.a.TextResultsetReader.read(TextResultsetReader.java:87)
	  at com.mysql.cj.protocol.a.TextResultsetReader.read(TextResultsetReader.java:48)
	  at com.mysql.cj.protocol.a.NativeProtocol.read(NativeProtocol.java:1601)
	  at com.mysql.cj.protocol.a.NativeProtocol.readAllResults(NativeProtocol.java:1655)
	  at com.mysql.cj.NativeSession.loadServerVariables(NativeSession.java:765)
	  at com.mysql.cj.jdbc.ConnectionImpl.initializePropsFromServer(ConnectionImpl.java:1303)
	  at com.mysql.cj.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:964)
	  at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:823)
	  - locked <0x4b9> (a com.mysql.cj.jdbc.ConnectionImpl)
	  at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:453)
	  at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:246)
	  at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:198)
	  at java.sql.DriverManager.getConnection(DriverManager.java:664)
	  at java.sql.DriverManager.getConnection(DriverManager.java:247)
	  at com.lz.testcase.jdbc.NomalDemo.selectNormal(NomalDemo.java:44)
	  at com.lz.testcase.jdbc.NomalDemo.main(NomalDemo.java:97)


         */
        /*
         * "main@1" prio=5 tid=0x1 nid=NA runnable
         *   java.lang.Thread.State: RUNNABLE
         * 	  at com.mysql.cj.protocol.a.result.ResultsetRowsStatic.<init>(ResultsetRowsStatic.java:56)
         * 	  at com.mysql.cj.protocol.a.BinaryResultsetReader.read(BinaryResultsetReader.java:98)
         * 	  at com.mysql.cj.protocol.a.BinaryResultsetReader.read(BinaryResultsetReader.java:50)
         * 	  at com.mysql.cj.protocol.a.NativeProtocol.read(NativeProtocol.java:1601)
         * 	  at com.mysql.cj.protocol.a.NativeProtocol.readAllResults(NativeProtocol.java:1655)
         * 	  at com.mysql.cj.ServerPreparedQuery.readExecuteResult(ServerPreparedQuery.java:392)
         * 	  at com.mysql.cj.ServerPreparedQuery.serverExecute(ServerPreparedQuery.java:208)
         * 	  at com.mysql.cj.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:631)
         * 	  - locked <0x57e> (a com.mysql.cj.jdbc.ConnectionImpl)
         * 	  at com.mysql.cj.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:417)
         * 	  at com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:1003)
         * 	  at com.lz.testcase.jdbc.NomalDemo.selectNormal(NomalDemo.java:48)
         * 	  at com.lz.testcase.jdbc.NomalDemo.main(NomalDemo.java:60)
         */
        while (resultSet.next()) {
            System.out.println(resultSet.getString(1));
        }
        long end = System.currentTimeMillis();
        System.out.println("selectStream span time="+(end-begin) + "ms");
        resultSet.close();
        statement.close();
        connection.close();
    }

    public static void main(String[] args) throws Exception {
        selectNormal();
        // selectStream();
    }
}
